USE NTDB
GO

IF OBJECT_ID('tableOfTablesColumnsNTDB') IS NOT NULL
    DROP TABLE tableOfTablesColumnsNTDB
IF OBJECT_ID('tableOfTablesColumnsNTDB2') IS NOT NULL
    DROP TABLE tableOfTablesColumnsNTDB2
IF OBJECT_ID('tableOfTablesColumnsNTDB3') IS NOT NULL
    DROP TABLE tableOfTablesColumnsNTDB3    

CREATE TABLE tableOfTablesColumnsNTDB (
RowID int not null primary key identity(1,1),
sName VARCHAR(50),
tName VARCHAR(50),
cName VARCHAR(50)
)

SET NOCOUNT ON
INSERT INTO tableOfTablesColumnsNTDB
SELECT s.name SchemaName, t.name TableName, c.name ColumnName
FROM sys.columns c INNER JOIN
     sys.tables t ON c.object_id = t.object_id INNER JOIN
     sys.schemas s ON t.schema_id = s.schema_id
     
WHERE t.name != 'tableOfTablesColumnsNTDB'

--SELECT * FROM tableOfTablesColumnsNTDB

DECLARE @ColumnName NVARCHAR(100)
DECLARE @TableName NVARCHAR(100)
DECLARE @RowsToProcess  INT
DECLARE @CurrentRow     INT
DECLARE @sql NVARCHAR(4000)

SET @RowsToProcess=@@ROWCOUNT

CREATE TABLE tableOfTablesColumnsNTDB2(
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
nRowsAll INT
)

CREATE TABLE tableOfTablesColumnsNTDB3(
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
nRowsNonNull INT
)

SET @CurrentRow = 0
WHILE @CurrentRow < @RowsToProcess
BEGIN
	SET @CurrentRow=@CurrentRow+1
	PRINT 'Processing row '+CONVERT(VARCHAR,@CurrentRow)+' of '+CONVERT(VARCHAR,@RowsToProcess);

    SELECT @TableName = tName, @ColumnName = cName FROM tableOfTablesColumnsNTDB WHERE @CurrentRow = RowID
   
    IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'INC_KEY' AND Object_ID = Object_ID(@TableName))
		BEGIN
			SELECT @sql = N'SELECT @cnt_all = COUNT(DISTINCT INC_KEY) FROM dbo.'+@TableName
		END
	ELSE
		BEGIN
			SELECT @sql = N'SELECT @cnt_all = COUNT(*) FROM dbo.'+@TableName
		END
		
	INSERT tableOfTablesColumnsNTDB2(TableName,ColumnName,nRowsAll) 
						      EXEC('DECLARE @cnt_all INT
							  EXEC sp_executesql N''' + @sql + ''',
						      N''@TableName nvarchar(100), @ColumnName nvarchar(100),
							  @cnt_all INT OUTPUT'',
                              @TableName = ''' + @TableName + ''',
                              @ColumnName = ''' + @ColumnName + ''',
                              @cnt_all = @cnt_all OUTPUT
							  SELECT ''' + @TableName+ ''',''' +@ColumnName+''',@cnt_all')

--	SELECT @sql = N'SELECT INC_KEY FROM dbo.'+@TableName+' WHERE '+@ColumnName+' IS NOT NULL'
--	INSERT tempTable(INC_KEY)
--	EXEC sp_executesql @sql
--	SELECT * FROM tempTable
	
--	SELECT @sql = N'SELECT @cnt_nonNull = COUNT('+@ColumnName+') FROM dbo.'+@TableName

	IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'INC_KEY' AND Object_ID = Object_ID(@TableName))
		BEGIN
			SELECT @sql = N'SELECT @cnt_nonNull = COUNT(DISTINCT INC_KEY) FROM dbo.'+@TableName+' WHERE '+@ColumnName+' IS NOT NULL'
		END
	ELSE
		BEGIN
			SELECT @sql = N'SELECT @cnt_nonNull = COUNT(*) FROM dbo.'+@TableName+' WHERE '+@ColumnName+' IS NOT NULL'
		END
		
	INSERT tableOfTablesColumnsNTDB3(TableName,ColumnName,nRowsNonNull) 
						      EXEC('DECLARE @cnt_nonNull INT
							  EXEC sp_executesql N''' + @sql + ''',
						      N''@TableName nvarchar(100), @ColumnName nvarchar(100),
							  @cnt_nonNull INT OUTPUT'',
                              @TableName = ''' + @TableName + ''',
                              @ColumnName = ''' + @ColumnName + ''',
                              @cnt_nonNull = @cnt_nonNull OUTPUT
							  SELECT ''' + @TableName+ ''',''' +@ColumnName+''',@cnt_nonNull')
         
--	SELECT @mycnt = cnt FROM #result
--    INSERT INTO tableOfTablesColumnsNTDB2(TableName,ColumnName) VALUES(@TableName,@ColumnName)
--    SELECT @sql = 'INSERT INTO tableOfTablesColumnsNTDB2(TableName, ColumnName) VALUES(' + @TableName + ', ' + @ColumnName+')'
--    PRINT @sql
--    EXEC(@sql)
--    EXECUTE sp_executesql @sql, @ParmDefinition, @CurrentRowVar = @CurrentRow;
--    EXECUTE sp_executesql N'INSERT INTO tableOfTablesColumnsNTDB2(TableName, ColumnName) VALUES(@TableName,@ColumnName)'
--	INSERT INTO tableOfTablesColumnsNTDB2(TableName,ColumnName,nRows) VALUES(@TableName,@ColumnName,)
END

--SELECT * FROM tableOfTablesColumnsNTDB2
--SELECT * FROM tableOfTablesColumnsNTDB3
SELECT Table1.TableName, Table1.ColumnName, Table1.nRowsAll, Table2.nRowsNonNull FROM tableOfTablesColumnsNTDB2 AS Table1, tableOfTablesColumnsNTDB3 AS Table2 
WHERE Table1.TableName = Table2.TableName AND Table1.ColumnName = Table2.ColumnName
--SELECT a.TableName, a.ColumnName, b.nRows FROM tableOfTablesColumnsNTDB2 a, tableOfTablesColumnsNTDB3 b

/*
SELECT @ColumnName = min(ColumnName) FROM tableOfTablesColumnsNTDB
SELECT @TableName = min(TableName) FROM tableOfTablesColumnsNTDB

WHILE @ColumnName IS NOT NULL
BEGIN
    SELECT * FROM tableOfTablesColumnsNTDB WHERE ColumnName = @ColumnName
    SELECT @ColumnName = MIN(ColumnName) FROM tableOfTablesColumnsNTDB WHERE ColumnName > @ColumnName
END
*/
/*EXEC CountRowsInThisColumn 'ColumnName', 'TableName', 'tableOfTablesColumnsNTDB'*/
GO

DROP TABLE tableOfTablesColumnsNTDB
DROP TABLE tableOfTablesColumnsNTDB2
DROP TABLE tableOfTablesColumnsNTDB3